<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/12/27
 * Time: 11:24
 */

class Order_goods_model extends CI_Model
{
    public $limit_rows = 15;

    public function goods_list($order_id)
    {
        $this->db->select('og.*,gs.sku_sn,g.goods_sn,gt.cat_name,gt.cat_name_zh,g.original_img, gu.supp_id, gu.supp_url,gu.in_price,gs.sku_number')
            ->from('order_goods AS og')
            ->join('goods_sku AS gs', 'og.sku_id = gs.sku_id', 'left')
            ->join('goods AS g', 'og.goods_id = g.goods_id', 'left')
            ->join('goods_type as gt', 'g.type_id = gt.cat_id', 'left')
            ->join('goods_supp AS gu', 'g.goods_id = gu.goods_id AND gu.is_host = 1', 'left')
            ->where('og.order_id', $order_id);

        $query = $this->db->get();


        $_list = $query->result_array();

        $list = array();

        foreach ($_list as $k => $item) {

            $s_query = $this->db->from('shelves_sku')->select('sl_title')->where("sku_id = {$item['sku_id']} and is_del = 0")->get();

            $item['shelves'] = $s_query->result_array();

            $list[$item['sku_id']] = $item;
        }

        return $list;
    }

    public function goods_info($rec_id)
    {
        $query = $this->db
            ->select('og.*,oi.order_status,oi.shipping_status,oi.pay_status,gs.sku_number,gu.in_price,gu.supp_id')
            ->from('order_goods AS og')
            ->join('goods_sku AS gs', 'og.sku_id = gs.sku_id', 'left')
            ->join('order_info AS oi', 'og.order_id = oi.order_id', 'left')
			->join('goods AS g', 'og.goods_id = g.goods_id', 'left')
			 ->join('goods_supp AS gu', 'g.goods_id = gu.goods_id AND gu.is_host = 1', 'left')
            ->where('og.rec_id', $rec_id)->get();

        return $query->row_array();
    }

    private function _where($param = array())
    {
        if ($param['goods_name']) {
            $this->db->where("(og.sku_num_sn like '%{$param['goods_name']}%' OR oi.order_sn = '{$param['goods_name']}')");
        }

        if ($param['status'] > -1) {
            $this->db->where("og.status", $param['status']);
        }

        if ($param['order_status'] > -1) {
            $this->db->where("oi.order_status", $param['order_status']);
        }
    
        if ($param['shipping_status'] > -1) {
            $this->db->where("oi.shipping_status", $param['shipping_status']);
        }
    }

    public function get_goods_list($param = array())
    {
        $this->db->from('order_goods AS og')
            ->select('og.*,g.original_img,oi.order_sn,oi.order_amount,oi.shipping_status,oi.order_status')
            ->join('goods AS g', 'og.goods_id = g.goods_id', 'left')
            ->join('order_info AS oi', 'og.order_id = oi.order_id', 'left')
            ->order_by('oi.order_amount ASC')
            ->limit($this->limit_rows,$this->limit_rows * $param['page']);

        $this->_where($param);

        $query = $this->db->get();

        return $query->result_array();
    }

    public function count($param = array())
    {
        $this->db->from('order_goods AS og')
            ->join('goods AS g', 'og.goods_id = g.goods_id', 'left')
            ->join('order_info AS oi', 'og.order_id = oi.order_id', 'left');

        $this->_where($param);

        return $this->db->count_all_results();
    }

    public function goods_sales($param = array())
    {
        $this->db->select('oi.order_sn,oi.order_amount,oi.landing_site,oi.add_time,oi.referring_site,og.sku_num_sn,(og.goods_number*og.goods_price) AS goods_price');
        $this->db->from('order_goods AS og');
        $this->db->join('order_info AS oi', 'oi.order_id = og.order_id', 'left');

        if ($param['start_time'] && $param['end_time']) {
            $this->db->where('oi.add_time >= ', $param['start_time']);
            $this->db->where('oi.add_time <= ', $param['end_time']);
        }
        $query = $this->db->get();

        return $query->result_array();
    }

    public function release_occupy_inventory_num($rec_id, $occupy_inventory_num = 0, $order_id = 0, $status = 1)
    {

        $this->db->set('occupy_inventory_num', "occupy_inventory_num-{$occupy_inventory_num}", FALSE);
        $this->db->set('status', $status);
        $this->db->where('rec_id', $rec_id);
        $this->db->update('order_goods');

        $this->db->set('order_status', 1);
        $this->db->where('order_id', $order_id);
        $this->db->update('order_info');

        return $occupy_inventory_num;
    }

    public function sku_goods_num($sku_num_sn, $time = array())
    {
        $this->db->select('SUM(og.goods_number) AS goods_number');
        $this->db->from('order_goods AS og');
        $this->db->join('order_info AS oi', 'og.order_id = oi.order_id', 'left');
        $this->db->where('og.sku_num_sn', $sku_num_sn);
        if ($time['start']) {
            $this->db->where('oi.add_time >= ', $time['start']);
        }

        $query = $this->db->get();
        $sku_goods = $query->row_array();
        return $sku_goods['goods_number'];
    }

    public function get_need_inventory_goods($sku_id)
    {
        $this->db->select('ELT(INTERVAL(oi.`goods_amount`,0, 200), 1, 2) AS yb_level,og.*, (og.goods_number - og.occupy_inventory_num) AS need_number,oi.order_sn');
        $this->db->from('order_goods AS og');
        $this->db->join('order_info AS oi', 'og.order_id = oi.order_id', 'left');
        $this->db->join('siteweb AS w', 'oi.web_id = w.web_id', 'left');
        $this->db->where("og.sku_id = '{$sku_id}' AND og.goods_number > og.occupy_inventory_num");

        $this->db->where("w.is_delete = 0 AND oi.order_status = 1 AND oi.shipping_status = 0 AND oi.pay_status = 2  AND oi.is_wrong = 0 AND og.status <= 2 and oi.add_time >= '1547913600'");

        $this->db->order_by('yb_level DESC,oi.order_sn ASC');

        $query = $this->db->get();

        $order_goods = $query->result_array();

        return $order_goods;
    }

    public function  allot_pg_number($data_storage)
    {
        $surplus_number = $data_storage['storage_number'];
        $add_time = time();
        $order_ids = array();

        $order_goods = $this->get_need_inventory_goods($data_storage['sku_id']);

        foreach ($order_goods as $row) {

            if ($surplus_number <= 0) {
                $surplus_number = 0;
                break;
            }

            $need_number = $surplus_number >= $row['need_number'] ? $row['need_number'] : $surplus_number;
            $surplus_number -= $need_number;

            //
            if ($data_storage['storage_id']) {
                $data_log = array(
                    'storage_id' => $data_storage['storage_id'],
                    'type' => 2,
                    'allot_id' => $row['rec_id'],
                    'allot_number' => $need_number,
                    'add_time' => $add_time
                );
                $this->db->insert('purchase_storage_log', $data_log);
            }
            
            $this->db->set('occupy_inventory_num', 'occupy_inventory_num+'.$need_number, false);

            if ($need_number == $row['need_number']) {
                $this->db->set('status', 3);
            }
            $this->db->where('rec_id', $row['rec_id']);
            $this->db->update('order_goods');

            $order_ids[$row['order_id']] = $row['order_id'];

            $rec_ids[$row['rec_id']] = array(
                'order_id' => $row['order_id'],
                'need_number' => $need_number
            );

        }
        
        $occupy_number = $surplus_number <= 0 ? $data_storage['storage_number'] : ($data_storage['storage_number'] - $surplus_number);

        return array('surplus_number' => $surplus_number, 'occupy_number' => $occupy_number, 'order_ids' => $order_ids, 'rec_ids' => $rec_ids);
    }
    
    public function inv_goods_number($rec_id, $number = array())
    {
        $need_number = $number['sku_number'] >= $number['need_number'] ? $number['need_number'] : $number['sku_number'];

        $this->db->set('occupy_inventory_num', 'occupy_inventory_num+'.$need_number, false);
        if ($number['sku_number'] >= $number['need_number']) {
            $this->db->set('status', 3);
        }
        $this->db->where('rec_id', $rec_id);
        $this->db->update('order_goods');

        return $need_number;
    }

    public function not_purchase_order_goods()
    {
        $this->db->select('og.*,g.goods_sn,g.original_img, gu.supp_id, gu.supp_url,gu.in_price');
        $this->db->from('order_goods AS og');
        $this->db->join('goods AS g', 'og.goods_id = g.goods_id', 'left');
        $this->db->join('goods_supp AS gu', 'g.g_supp_id = gu.g_supp_id', 'left');
        $this->db->where('og.goods_number > og.occupy_inventory_num AND (SELECT COUNT(1) FROM `pcs_purchase_goods_log` AS pgl WHERE pgl.`rec_id` = og.`rec_id`) <= 0');
        $query = $this->db->get();
        $order_goods = $query->result_array();

        return $order_goods;
    }

    public function get_order_goods_sales($sku_id, $time = array())
    {

        $this->db->where("og.sku_id = '$sku_id'");

        if ($time['start']) {
            $this->db->where("oi.add_time >= '{$time['start']}'");
        }

        if ($time['end']) {
            $this->db->where("oi.add_time <= '{$time['end']}'");
        }

        $query = $this->db
            ->from('order_goods AS og')
            ->join('order_info AS oi', 'og.order_id = oi.order_id', 'left')
            ->select_sum('og.goods_number')
            ->get();

        $order_sum = $query->row_array();

        return intval($order_sum['goods_number']);
    }
    
    public function get_order_goods_sales_list($param = array())
    {
        
        if ($param['web_id']) {
            $this->db->where("oi.web_id >= '{$param['web_id']}'");
        }
        
        if ($param['order_start']) {
            $this->db->where("oi.add_time >= '{$param['order_start']}'");
        }
        
        if ($param['order_end']) {
            $this->db->where("oi.add_time <= '{$param['order_end']}'");
        }
        
        $query = $this->db
            ->from('order_goods AS og')
            ->join('order_info AS oi', 'og.order_id = oi.order_id', 'left')
            ->join('goods_sku AS gs', 'og.sku_id = gs.sku_id', 'left')
            ->select('og.goods_id,gs.color, SUM(og.goods_number) as goods_number')
            ->group_by(array('og.goods_id', 'gs.color'))
            ->get();
        
        $_list = $query->result_array();
    
        $order_goods = array();
        foreach ($_list as $item) {
            $order_goods["{$item['goods_id']}-{$item['color']}"] = $item;
        }
        
        return $order_goods;
    }
}
